package com.sunwayworld.baseline.framework.utils;

import com.sunwayworld.framework.utils.NumberUtils;
import com.sunwayworld.framework.utils.ObjectUtils;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;

import java.lang.reflect.Field;
import java.sql.*;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Properties;

public class AccessHelper {

    private static final Logger logger = LogManager.getLogger(AccessHelper.class);

    private Connection connection;
    private Statement statement;
    private ResultSet resultSet;
    private String dbFilePath;
    private String user;
    private String password;

    /**
     * 构造函数
     *
     * @param dbFilePath sqlite db 文件路径
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public AccessHelper(String dbFilePath, String user, String password) throws ClassNotFoundException, SQLException {
        this.dbFilePath = dbFilePath;
        if (user == null) user = "";
        if (password == null) password = "";
        this.user = user;
        this.password = password;
        connection = getConnection(dbFilePath, user, password);
    }

    /**
     * 获取数据库连接
     *
     * @param dbFilePath db文件路径
     * @return 数据库连接
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public Connection getConnection(String dbFilePath, String user, String password) throws ClassNotFoundException, SQLException {
        Connection conn = null;
        Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
        Properties p = new Properties();
        p.put("charSet", "gbk");
        p.put("user", user);
        p.put("password", password);
        conn = DriverManager.getConnection("jdbc:ucanaccess://" + dbFilePath + ";memory=false",p);
        return conn;
    }

    /**
     * 执行sql查询
     *
     * @param sql sql select 语句
     * @param rse 结果集处理类对象
     * @return 查询结果
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public <T> T executeQuery(String sql, ResultSetExtractor<T> rse) throws SQLException, ClassNotFoundException {
        try {
            resultSet = getStatement().executeQuery(sql);
            T rs = rse.extractData(resultSet);
            return rs;
        } finally {
            destroyed();
        }
    }

    /**
     * 执行select查询，返回结果列表
     *
     * @param sql sql select 语句
     * @param rm  结果集的行数据处理类对象
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public <T> List<T> executeQuery(String sql, RowMapper<T> rm) throws SQLException, ClassNotFoundException {
        List<T> rsList = new ArrayList<T>();
        try {
            resultSet = getStatement().executeQuery(sql);
            while (resultSet.next()) {
                rsList.add(rm.mapRow(resultSet, resultSet.getRow()));
            }
        } finally {
            destroyed();
        }
        return rsList;
    }

    /**
     * 执行数据库更新sql语句
     *
     * @param sql
     * @return 更新行数
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public int executeUpdate(String sql) throws SQLException, ClassNotFoundException {
        try {
            int c = getStatement().executeUpdate(sql);
            return c;
        } finally {
            destroyed();
        }

    }

    /**
     * 执行多个sql更新语句
     *
     * @param sqls
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public void executeUpdate(String... sqls) throws SQLException, ClassNotFoundException {
        try {
            for (String sql : sqls) {
                getStatement().executeUpdate(sql);
            }
        } finally {
            destroyed();
        }
    }

    /**
     * 执行数据库更新 sql List
     *
     * @param sqls sql列表
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public void executeUpdate(List<String> sqls) throws SQLException, ClassNotFoundException {
        try {
            for (String sql : sqls) {
                getStatement().executeUpdate(sql);
            }
        } finally {
            destroyed();
        }
    }

    public Connection getConnection() throws ClassNotFoundException, SQLException {
        if (null == connection) connection = getConnection(dbFilePath, user, password);
        return connection;
    }

    private Statement getStatement() throws SQLException, ClassNotFoundException {
        if (null == statement) {
            statement = getConnection().createStatement();
        }
        return statement;
    }

    /**
     * 数据库资源关闭和释放
     */
    public void destroyed() {
        try {
            if (null != statement) {
                statement.close();
                statement = null;
            }

            if (null != connection) {
                connection.close();
                connection = null;
            }

            if (null != resultSet) {
                resultSet.close();
                resultSet = null;
            }
        } catch (SQLException e) {
            logger.error("Sqlite数据库关闭时异常", e);
        }
    }

    /**
     * 执行select查询，返回结果列表
     *
     * @param sql   sql select 语句
     * @param clazz 实体泛型
     * @return 实体集合
     * @throws SQLException           异常信息
     * @throws ClassNotFoundException 异常信息
     */
    public <T> List<T> executeQueryList(String sql, Class<T> clazz) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException {
        List<T> rsList = new ArrayList<T>();
        try {
            resultSet = getStatement().executeQuery(String.valueOf(sql));
            while (resultSet.next()) {
                T t = clazz.newInstance();
                for (Field field : t.getClass().getDeclaredFields()) {
                    field.setAccessible(true);
                    String string = new String(field.getName().getBytes("GBK"));
//                    Object object = resultSet.getObject(field.getName());
                    Object object = resultSet.getObject(string);
                    if(!ObjectUtils.isEmpty(object)){
                        Class<?> type = field.getType();
                        if(type.equals(LocalDateTime.class)) {
                            LocalDateTime localDateTime = getLocalDateTime(object);
                            if(!ObjectUtils.isEmpty(localDateTime)){
                                field.set(t, localDateTime);
                            }
                        } else if(type.equals(Long.class)) {
                            Long lBean = getLong(object);
                            if(!ObjectUtils.isEmpty(lBean)){
                                field.set(t, lBean);
                            }
                        } else {
                            field.set(t, object.toString());
                        }
                    }
                }
                rsList.add(t);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            destroyed();
        }
        return rsList;
    }

    private Long getLong(Object object) {

        if(NumberUtils.isNumber(object.toString())){
            return Long.parseLong(object.toString());
        }

        return null;
    }

    private LocalDateTime getLocalDateTime(Object object) {

        LocalDateTime result = null;
        if(object instanceof Timestamp){
            Timestamp ts = (Timestamp)object;
            long timeMillis = ts.getTime();
            // LocalDateTime.
            // result = LocalDateTime.parse(object.toString());
            ZoneId zoneId = ZoneId.systemDefault();
            result = Instant.ofEpochMilli(timeMillis).atZone(zoneId).toLocalDateTime();
        }

        return result;
    }

    /**
     * 执行sql查询,适用单条结果集
     *
     * @param sql   sql select 语句
     * @param clazz 结果集处理类对象
     * @return 查询结果
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public <T> T executeQuery(String sql, Class<T> clazz) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException {
        try {
            resultSet = getStatement().executeQuery(sql);
            T t = clazz.newInstance();
            for (Field field : t.getClass().getDeclaredFields()) {
                field.setAccessible(true);
                field.set(t, resultSet.getObject(field.getName()));
            }
            return t;
        } finally {
            destroyed();
        }
    }

    /**
     * 执行数据库更新sql语句
     *
     * @param tableName 表名
     * @param param     key-value键值对,key:表中字段名,value:值
     * @return 更新行数
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public int executeInsert(String tableName, Map<String, Object> param) throws SQLException, ClassNotFoundException {
        try {
            StringBuffer sql = new StringBuffer();
            sql.append("INSERT INTO ");
            sql.append(tableName);
            sql.append(" ( ");
            for (String key : param.keySet()) {
                sql.append(key);
                sql.append(",");
            }
            sql.delete(sql.length() - 1, sql.length());
            sql.append(")  VALUES ( ");
            for (String key : param.keySet()) {
                sql.append("'");
                sql.append(param.get(key));
                sql.append("',");
            }
            sql.delete(sql.length() - 1, sql.length());
            sql.append(");");
            int c = getStatement().executeUpdate(sql.toString());
            return c;
        } finally {
            destroyed();
        }


    }
}
